﻿CREATE PROCEDURE [dbo].[sp_Get_LeadsByLeadBlockCodeId]
	@ZipID varchar(max)
AS
	declare @agentName varchar(200)
	
	select @agentName = max(substring(AgentName, CHARINDEX(' ', AgentName) + 1, LEN(AgentName))) 
	from ZipInfo 
	where (AgentName is not null or AgentName <> '')
	and ZipID IN (select subcolstr from udf_CharlistToTable(@ZipID, ','))
		
	select l.[LeadID]
      ,l.[SelPersonID]
      ,l.[Zip]
      ,l.[ZipRequesterCode]
      ,l.[Division]
      ,l.[Market]
      ,l.[Territory]
      ,l.[Zip4]
      ,l.[CallType]
      ,l.[LeadType]
      ,l.[MdNumber]
      ,l.[SequenceNumber]
      ,l.[RawData]
      ,l.[City]
      ,l.[State]
      ,l.[County]
      ,l.[Latitude]
      ,l.[Longitude]
      ,z.ZipID
		from dbo.Zips z
		inner join ZipInfo zi on z.ZipID = zi.ZipID
		inner join dbo.Leads l on l.ZipRequesterCode = z.Zip
		where zi.AgentName in
		(select subcolstr 
		from udf_CharlistToTable('1 ' + @agentName + ',' + '2 ' + @agentName + ',' + '3 ' + @agentName + ',' + '4 ' + @agentName + ',' + '5 ' + @agentName, ','))